package com.hy.dao;

import com.hy.bean.Messages;
import com.hy.bean.UmsUser;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class MessagesDao {
    private QueryRunner qr = new QueryRunner();

    public   UmsUser  selectId(String user_name) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where user_name = ? ";
        BeanHandler<UmsUser> bhl = new BeanHandler<>(UmsUser.class);
        UmsUser query = qr.query(conn, sql, bhl, user_name);
        DbUtils.close(conn);
        return query;
    }
    public  List<Map<String, Object>>  select(int user_id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * FROM ums_user as u inner JOIN messages  as m  on  u.id = m.user1_id where m.user_id = ?";
        MapListHandler mlh = new MapListHandler();
        List<Map<String, Object>> mapList = qr.query(conn, sql, mlh, user_id);
        System.out.println(mapList.toString());

        DbUtils.close(conn);
        return mapList;
    }
    public boolean add(Messages m) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "insert into messages values(?,?,?,?,null,null,?,null)";


        int count = qr.update(conn,sql,m.getUser_id(),m.getUser1_id(),m.getCreate_time(),m.getCreate_id(),m.getContent());
        DbUtils.close(conn);
        return  count>0?true:false;
    }

    public  List<Map<String, Object>>  select1(String user_name) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from  ums_user where id in (\n" +
                "select user1_id from ums_user u INNER JOIN messages  m on u.id = m.user_id where u.user_name = ? ORDER BY u.create_time desc\n" +
                ")";
        MapListHandler mlh = new MapListHandler();
        List<Map<String, Object>> mapList = qr.query(conn, sql, mlh, user_name);

        DbUtils.close(conn);
        return mapList;
    }

    public  List<Map<String, Object>>  select2(int user1_id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select  m.create_time,content  from ums_user u INNER JOIN messages  m  on u.id = m.user_id  where user1_id = ?";
        MapListHandler mlh = new MapListHandler();
        List<Map<String, Object>> mapList = qr.query(conn, sql, mlh, user1_id);

        DbUtils.close(conn);
        return mapList;
    }

    public  List<Map<String, Object>>  select3(String user_name) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * FROM ums_user as u inner JOIN messages  as m  on  u.id = m.user1_id where user_name like ?";
        MapListHandler mlh = new MapListHandler();
        List<Map<String, Object>> mapList = qr.query(conn, sql, mlh, '%'+user_name+'%');
        System.out.println(sql);
        DbUtils.close(conn);
        return mapList;
    }
    public boolean insert(Messages m) throws Exception {
        //1,2
        Connection conn = DBManager.getConn();

        //3 手动下划线 转驼峰标示
        String sql = "insert into messages values(?,?,?,?,?,?,?,?)";

        SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();

        int count = qr.update(conn, sql, m.getUser_id(),m.getUser1_id(),formatter.format(date),m.getCreate_id(),null,null,m.getContent(),null);

        DbUtils.close(conn);

        return count>0?true:false;
    }

    public static void main(String[] args) throws Exception {
        MessagesDao m = new MessagesDao();
        System.out.println(m.select3("f"));
    }

}
